# -*- coding: utf-8 -*-

"""
Date: 2023/2/27 11:08
Author: Darker
"""
import math
import pathlib
from typing import List, NoReturn, Text, Any

from matplotlib import pyplot as plt
import numpy as np
from matplotlib.ticker import MultipleLocator

from config import conf
from log import logger
from utils import date_utils
from utils.model import mySqlite


def pict(tool: List, label: List, color: List) -> NoReturn:
    """
    画图
    :param tool: 数据列表
    :param label: label标签列表
    :param color: local标签颜色列表
    :return:
    """
    plt.rcParams["font.family"] = ["SimHei"]  # 设置字体用于显示中文
    bottom = len(tool[0]) * [0]  # 堆积柱子的基底
    i1 = 0  # label标签序列以及标签颜色序列
    x = tool[0]
    ab1 = plt.figure(figsize=(16, 6.5))  # 初始化画布尺寸
    ab1.gca().spines["top"].set_color("none")  # 去除图形上边界
    ab1.gca().spines["left"].set_color("none")  # 去除图形左边界
    ab1.gca().spines["right"].set_color("none")  # 去除图形右边界
    plt.grid(axis="y", color="#dfdfdf")  # 设置图形网格线
    maxNum = 0  # 最大数字
    for i in tool[1:]:
        if maxNum < max(i):
            maxNum = max(i)
        plt.bar(x, i, bottom=bottom, label=label[i1], color=color[i1], zorder=10)  # 堆积设置柱子
        if len(tool) > 2:  # 如果是堆积柱子，则显示每个子柱子的数量，白色字体展示
            for a, b, c in zip(x, bottom, i):  # x: 显示内容的x轴; bottom+i/2: 显示内容的y轴; i: 显示的内容
                if c != 0:
                    plt.text(a, b + c / 2, c, zorder=20, color="white")  # 如果i不等于0就显示每个堆积柱子的数字
        bottom = np.array(bottom) + np.array(i)  # 更新堆积柱子基底
        i1 += 1
    if maxNum == 0:  # 如果最大数字为0，说明没有一个柱子高度有值，则没必要生成图片，退出函数
        logger.info(f"图表【{label[-1]}】无数据，无需生成")
        return
    ab1.gca().yaxis.set_major_locator(MultipleLocator(math.ceil(maxNum / 7)))  # 设置Y轴刻度间隔
    for a, b in zip(x, bottom):
        plt.text(a, b + 0.05, b, fontsize=12)  # 展示每列总数
    plt.title(label[-1], fontsize=16, y=1.1)  # 设置图形标题
    plt.legend(loc=(1.01, 0.9))  # 设置label标签位置
    if len(tool[0]) > 13:
        plt.xticks(rotation=50)
    logger.info(f"生成【{label[-1]}】成功\t✔️")
    pathSavefig = pathlib.Path(conf.pictureDir).joinpath(rf"{label[-1]}.png")
    plt.savefig(pathSavefig, bbox_inches="tight", pad_inches=0.3)
    plt.close()


def analyze_datas(sqlList: List, dateList: List, projectId: Text) -> List[Any]:
    """
    分析数据，存表并画图
    :param sqlList: sql列表
    :param dateList: 时间轴
    :param projectId: 项目id
    :return:
    """
    createTableSql = """create table scores
    (id TEXT,
    title TEXT,
    severity TEXT,
    status TEXT,
    current_owner TEXT,
    reporter TEXT,
    created TEXT,
    fixer TEXT,
    resolved TEXT,
    closed TEXT,
    reject_time TEXT,
    reopen_time TEXT,
    de TEXT,
    closer TEXT,
    priority TEXT,
    reject_num TEXT);"""
    with mySqlite:
        # 先建表
        mySqlite.execute(createTableSql)
        mySqlite.execute_many(sqlList)

        def get_numb(condition: Text) -> List:
            """生成趋势图方法"""
            return [mySqlite.execute(f"select count(1) from scores where "
                                     f"{condition.replace('%date%', dateSelected)};")[0][0]
                    for dateSelected in dateList]

        # 每日新增bug数趋势图
        pict([dateList, get_numb("created='%date%'")], ["新增缺陷数", "每日新增BUG数趋势图"], ["#63B8FF"])

        # 每日剩余bug趋势图
        # 每日未关闭总数量，开发已解决待回归，开发未解决
        def add1times(rejectTime, resolved, reopenTime, dateSelected):
            """计算是否在这个日子里是不是处于已处理的状态"""
            if not reopenTime:
                if resolved and resolved <= dateSelected or (rejectTime and rejectTime <= dateSelected):
                    return 1
            else:
                if reopenTime <= dateSelected:
                    if reopenTime <= rejectTime <= dateSelected:
                        return 1
                    elif reopenTime <= resolved < dateSelected:
                        return 1
                else:
                    if rejectTime and rejectTime <= dateSelected or (resolved and resolved <= dateSelected):
                        return 1
            return 0

        numAll: List = []
        numSolved: List = []
        numNew: List = []
        for dateSelected in dateList:
            sqlAds = f"select reject_time, resolved, reopen_time from scores where created <= '{dateSelected}' " \
                     f"and (closed = '' or closed > '{dateSelected}');"
            thisList = mySqlite.execute(sqlAds)
            numAll.append(len(thisList))
            numSolved.append(sum(add1times(*i, dateSelected) for i in thisList))
            if dateSelected == date_utils.today():
                sqlTwo = f"select count(1) from scores where created <= '{dateSelected}' and status in ('已验证', '已解决', " \
                         f"'已拒绝') and (closed = '' or closed > '{dateSelected}');"
                numSolved.pop(-1)
                numSolved.append(mySqlite.execute(sqlTwo)[0][0])
            numNew.append(numAll[-1] - numSolved[-1])
        pict([dateList, numSolved, numNew], ["已解决测试未关闭", "开发尚未解决", "每日剩余BUG趋势图"],
             ["#90EE90", "#ed7158"])

        def get_numb1(peopleList, condition):
            """生成缺陷处理人/创建人分布图方法"""
            if not peopleList:
                logger.info(f"图表【{label[-1]}】无数据，无需生成")
                return
            dateList = list(zip(*peopleList))[0]
            c3 = [[] for _ in range(len(label) - 1)]
            for i in range(len(dateList)):
                for j in range(len(label) - 1):
                    c3[j].append(mySqlite.execute(f"select count(1) from scores where "
                                                  f"{condition.replace('%date%', str(dateList[i]))} = '{label[j]}';")[0][0])
            dateList = list(dateList)
            dateList.extend(c3)
            pict([dateList], label, color)

        """测试数据"""
        # 测试今日创建BUG数分布图
        label = ["建议", "提示", "一般", "严重", "致命", "今日创建BUG数分布图"]
        color = ["#e7e5e5", "#90EE90", "#fed455", "#e9a661", "#ed7158"]
        sql1 = f"select reporter from scores where created = '{date_utils.today()}' " \
               f"group by reporter order by count(1) desc"
        sql2 = f"created = '{date_utils.today()}' and reporter = '%date%' and severity"
        get_numb1(mySqlite.execute(sql1), sql2)

        # 测试今日关闭BUG数分布图
        label = ["已关闭", "今日关闭BUG数分布图"]
        color = ["#63B8FF"]
        sql1 = f"select reporter from scores where closed = '{date_utils.today()}' group by reporter order by count(1) desc"
        sql2 = "reporter = '%date%' and severity"
        get_numb1(mySqlite.execute(sql1), sql2)

        # 测试创建BUG总数分布图
        label = ["建议", "提示", "一般", "严重", "致命", "创建BUG总数分布图"]
        color = ["#e7e5e5", "#90EE90", "#fed455", "#e9a661", "#ed7158"]
        sql1 = f"select reporter from scores group by reporter order by count(1) desc"
        sql2 = "reporter = '%date%' and severity"
        get_numb1(mySqlite.execute(sql1), sql2)

        # 测试待处理BUG对应处理人分布图
        label = ["已验证", "已解决", "已拒绝", "待处理BUG对应处理人分布图"]
        color = ["#90EE90", "#fed455", "#ed7158"]
        sql1 = f"select current_owner from scores where status in ('已验证', '已解决', '已拒绝') group by current_owner " \
               f"order by count(1) desc"
        sql2 = "current_owner = '%date%' and status"
        get_numb1(mySqlite.execute(sql1), sql2)

        """开发数据"""

        def get_numb2(sqlList, sql2):
            if not sqlList:
                logger.warn(f"图表【{label[-1]}】无数据，无需生成")
                return
            dateList = list(zip(*sqlList))[0]
            c3 = [[] for _ in range(len(label) - 1)]
            for i in range(len(dateList)):
                for j in range(len(label) - 1):
                    c3[j].append(mySqlite.execute(f"select count(1) from scores where {sql2[j]} = '{dateList[i]}';")[0][0])
            dateList = list(dateList)
            dateList.extend(c3)
            pict([dateList], label, color)

        # 生成开发今日产生BUG数分布图
        label = ["转需求", "已拒绝", "已解决", "保持为新", "被重新打开", "今日产生BUG数分布图"]
        color = ["#90EE90", "#e7e5e5", "#63B8FF", "#fed455", "#ed7158"]
        sql1 = f"select * from (select fixer as names from scores where status not in ('重新打开', '新', '确认产品bug') and " \
               f"created = '{date_utils.today()}' and resolved != '' union all select de as names from scores where status " \
               f"not in ('重新打开', '新', '确认产品bug') and created = '{date_utils.today()}' and resolved = '' and " \
               f"reject_time != '' union all select closer as names from scores where status not in ('重新打开', '新', " \
               f"'确认产品bug') and created = '{date_utils.today()}' and resolved = '' and reject_time = '' and closed != ''" \
               f"union all select current_owner as names from scores where status in ('新', '重新打开', '确认产品bug') and " \
               f"created = '{date_utils.today()}') group by names order by count(1) desc"
        sql2 = [
            f"status not in ('重新打开', '新', '确认产品bug') and created = '{date_utils.today()}' and resolved = '' and "
            f"reject_time = '' and closed != '' and closer",
            f"status not in ('重新打开', '新', '确认产品bug') and created = '{date_utils.today()}' and resolved = '' and "
            f"reject_time != '' and de",
            f"status not in ('重新打开', '新', '确认产品bug') and created = '{date_utils.today()}' and resolved = '' and fixer",
            f"status in ('新', '确认产品bug') and created = '{date_utils.today()}' and current_owner",
            f"status in ('重新打开') and created = '{date_utils.today()}' and current_owner"
        ]
        get_numb2(mySqlite.execute(sql1), sql2)

        # 生成开发今日解决BUG数分布图
        label = ["已解决", "已拒绝", "转需求", "今日解决BUG数分布图"]  # 标签参数  # 顺序敏感
        color = ["#63B8FF", "#e7e5e5", "#90EE90"]  # 颜色参数
        sql1 = f"select * from (select fixer as names from scores where status not in ('重新打开','新','确认产品bug') and " \
               f"resolved='{date_utils.today()}' union all  select de from scores where status not in ('重新打开','新','确认产品bug') and  " \
               f"resolved='' and reject_time='{date_utils.today()}' union all select closer from scores where status not in " \
               f"('重新打开','新','确认产品bug') and  resolved='' and reject_time='' and closed='{date_utils.today()}') group by names " \
               f"order by count(1) desc"
        sql2 = [
            f"status not in ('重新打开','新','确认产品bug') and resolved='{date_utils.today()}' and fixer",
            f"status not in ('重新打开','新','确认产品bug') and resolved='' and reject_time='{date_utils.today()}' and de",
            f"status not in ('重新打开','新','确认产品bug') and resolved='' and reject_time='' and closed='{date_utils.today()}' and closer"
        ]
        get_numb2(mySqlite.execute(sql1), sql2)

        # 生成开发累计解决BUG分布图
        label = ["已解决", "已拒绝", "转需求", "累计解决BUG分布图"]  # 标签参数  # 顺序敏感
        color = ["#63B8FF", "#e7e5e5", "#90EE90"]  # 颜色参数
        sql1 = f"select * from (select fixer as names from scores where status not in ('重新打开','新','确认产品bug') and " \
               f"resolved!='' union all  select de from scores where status not in ('重新打开','新','确认产品bug') and  " \
               f"resolved='' and reject_time!='' union all select closer from scores where status not in " \
               f"('重新打开','新','确认产品bug') and  resolved='' and reject_time='' and closed!='') group by names " \
               f"order by count(1) desc"
        sql2 = [
            f"status not in ('重新打开','新','确认产品bug') and resolved!='' and fixer",
            f"status not in ('重新打开','新','确认产品bug') and resolved='' and reject_time!='' and de",
            f"status not in ('重新打开','新','确认产品bug') and resolved='' and reject_time='' and closed!='' and closer"
        ]
        get_numb2(mySqlite.execute(sql1), sql2)

        # 生成累计BUG对应处理人分布图
        label = ["转需求", "已拒绝", "已解决", "保持为新", "被重新打开", "累计BUG对应处理人分布图"]  # 标签参数  # 顺序敏感
        color = ["#90EE90", "#e7e5e5", "#63B8FF", "#fed455", "#ed7158"]  # 颜色参数
        sql1 = f"select * from (select fixer as names from scores where status not in ('重新打开','新','确认产品bug') and " \
               f"resolved!='' union all  select de from scores where status not in ('重新打开','新','确认产品bug') and  " \
               f"resolved='' and reject_time!='' union all select closer from scores where status not in " \
               f"('重新打开','新','确认产品bug') and  resolved='' and reject_time='' and closed!='' union all select " \
               f"current_owner from scores where status in ('新','重新打开','确认产品bug')) group by names order by count(1) desc"
        sql2 = [
            f"status not in ('重新打开','新','确认产品bug') and  resolved='' and reject_time='' and closed!='' and closer",
            f"status not in ('重新打开','新','确认产品bug') and resolved='' and reject_time!='' and de",
            f"status not in ('重新打开','新','确认产品bug') and resolved!='' and fixer",
            f"status in ('新','确认产品bug') and current_owner",
            f"status in ('重新打开') and current_owner"
        ]
        get_numb2(mySqlite.execute(sql1), sql2)

        # 生成累计BUG对应开发人员分布图
        label = ["转需求", "已拒绝", "已解决", "保持为新", "被重新打开", "累计BUG对应开发人员分布图"]  # 标签参数  # 顺序敏感
        color = ["#90EE90", "#e7e5e5", "#63B8FF", "#fed455", "#ed7158"]  # 颜色参数
        sql1 = f"select * from (select de as names from scores where status not in ('重新打开','新','确认产品bug') and " \
               f"resolved!='' union all  select de as names from scores where status not in ('重新打开','新','确认产品bug') " \
               f"and  resolved='' and reject_time!='' union all select de as names from scores where status not in " \
               f"('重新打开','新','确认产品bug') and  resolved='' and reject_time='' and closed!='' union all select de " \
               f"as names from scores where status in ('新','重新打开','确认产品bug')) group by names order by count(1) desc"
        sql2 = [
            f"status not in ('重新打开','新','确认产品bug') and  resolved='' and reject_time='' and closed!='' and de",
            f"status not in ('重新打开','新','确认产品bug') and resolved='' and reject_time!='' and de",
            f"status not in ('重新打开','新','确认产品bug') and resolved!='' and de",
            f"status in ('新','确认产品bug') and de",
            f"status in ('重新打开') and de"
        ]
        get_numb2(mySqlite.execute(sql1), sql2)

        # 重复打开缺陷个数排序
        label = ["返工一次", "返工两次", "两次以上", "重复打开BUG数对应开发人员分布图"]  # 标签参数  # 顺序敏感
        color = ["#63B8FF", "#fed455", "#ed7158"]  # 颜色参数
        sql1 = f"select * from (select de as names from scores where reject_num not in ('0', 0, '', '--')) group by names " \
               f"order by count(1) desc"
        sql2 = [
            f"reject_num = '1' and de",
            f"reject_num = '2' and de",
            f"reject_num not in ('0', 0, '', '--', '1', '2') and de"
        ]
        get_numb2(mySqlite.execute(sql1), sql2)

        # 生成开发待解决BUG对应处理人分布图√
        if projectId == "67410840":  # 如果是产品迭代,则没有'确认产品bug'
            label = ["重新打开", "新", "待解决BUG对应处理人分布图"]  # 顺序敏感
            color = ["#ed7158", "#fed455"]
            sql1 = f"select current_owner from scores where status in ('重新打开','新')  group by current_owner " \
                   f"order by count(1) desc"
            sql2 = f"current_owner='%date%' and status"
            get_numb1(mySqlite.execute(sql1), sql2)  # 待解决缺陷对应处理人分布图
        else:  # 其他项目则存在确认产品bug状态的缺陷
            label = ["重新打开", "确认产品bug", "新", "待解决BUG对应处理人分布图"]  # 顺序敏感
            color = ["#ed7158", "#e7e5e5", "#fed455"]
            sql1 = f"select current_owner from scores where status in ('重新打开','确认产品bug','新')  group by " \
                   f"current_owner order by count(1) desc"
            sql2 = f"current_owner='%date%' and status"
            get_numb1(mySqlite.execute(sql1), sql2)  # 待解决缺陷对应处理人分布图

        def get_numb3(sqlList, picName):
            """
            生成重新打开缺陷清单、阻塞缺陷清单的表格
            :return:
            """
            html = '<table border=1 cellpadding="6px"><tr bgcolor="#9bdde9"><td style=\'width:80px\' ' \
                   'align=\'center\'>缺陷ID</td><td style=\'width:450px\' align=\'center\'>缺陷标题</td><td ' \
                   'style=\'width:120px\' align=\'center\'>缺陷处理人</td><td style=\'width:120px\' ' \
                   'align=\'center\'>缺陷创建人</td></tr>'
            if not sqlList:
                logger.warn(f"图表【{picName}】无数据,无需生成")
                html = '<p>暂无</p>'
            else:
                logger.info(f"生成【{picName}】成功    √")
                for i in range(len(sqlList)):
                    html = f'{html}<tr><td align=\'center\' \'width:80px\'>{sqlList[i][0]}</td><td style=\'width:450px\'>' \
                           f'{sqlList[i][1]}</td><td style=\'width:120px\' align=\'center\'>{sqlList[i][2]}</td><td ' \
                           f'style=\'width:120px\' align=\'center\'>{sqlList[i][3]}</td></tr>'
                html += '</table>'  # f'{html}</table>'
            return html

        def get_numb4(sqlList, picName):
            """
            生成阻塞缺陷清单的表格
            :return:
            """
            html = '<table border=1 cellpadding="6px"><tr bgcolor="#9bdde9"><td style=\'width:80px\' ' \
                   'align=\'center\'>缺陷ID</td><td style=\'width:450px\' align=\'center\'>缺陷标题</td><td ' \
                   'style=\'width:120px\' align=\'center\'>缺陷处理人</td><td style=\'width:120px\' ' \
                   'align=\'center\'>缺陷创建人</td><td style=\'width:120px\' align=\'center\'>阻塞天数</td></tr>'
            if not sqlList:
                logger.warn(f"图表【{picName}】无数据,无需生成")
                html = '<p>暂无</p>'
            else:
                logger.info(f"生成【{picName}】成功    √")
                for i in range(len(sqlList)):
                    daysDiff = date_utils.days_diff(date_utils.today(), sqlList[i][4])
                    html = f'{html}<tr><td align=\'center\' \'width:80px\'>{sqlList[i][0]}</td><td style=\'width:450px\'>' \
                           f'{sqlList[i][1]}</td><td style=\'width:120px\' align=\'center\'>{sqlList[i][2]}</td><td ' \
                           f'style=\'width:120px\' align=\'center\'>{sqlList[i][3]}</td><td style=\'width:120px\' ' \
                           f'align=\'center\'>{str(daysDiff)}</td></tr>'
                html += '</table>'  # f'{html}</table>'
            return html

        # 生成阻塞缺陷清单
        sql1 = "select id,title,current_owner,reporter,created from scores where priority in ('紧急','高') and " \
               "status in ('重新打开','确认产品bug','新') order by created;"
        mail1 = get_numb4(mySqlite.execute(sql1), '阻塞缺陷清单')
        # 生成重新打开缺陷清单
        sql1 = "select id,title,current_owner,reporter from scores where status='重新打开';"
        mail2 = get_numb3(mySqlite.execute(sql1), '重新打开缺陷清单')
        numbs = [
            mySqlite.execute("select count(1) from scores;")[0][0],
            mySqlite.execute("select count(1) from (select fixer as names from scores where status not in "
                             "('重新打开','新','确认产品bug') and resolved='{date_utils.today()}' union all  "
                             "select de from scores where status not in ('重新打开','新','确认产品bug') and  resolved='' and "
                             "reject_time='{date_utils.today()}' union all select closer from scores where status not in "
                             "('重新打开','新','确认产品bug') and  resolved='' and reject_time='' and "
                             "closed='{date_utils.today()}');")[0][0],
            mySqlite.execute("select count(1) from (select fixer as names from scores where status not in "
                             "('重新打开','新','确认产品bug') and resolved!='' union all  select de from scores where "
                             "status not in ('重新打开','新','确认产品bug') and  resolved='' and reject_time!='' union all "
                             "select closer from scores where status not in ('重新打开','新','确认产品bug') and  resolved='' "
                             "and reject_time='' and closed!='');")[0][0],
            mySqlite.execute("select count(1) from scores where priority in ('紧急','高') and status in "
                             "('重新打开','确认产品bug','新');")[0][0],
            mail1,
            mySqlite.execute("select count(1) from scores where status='重新打开'")[0][0],
            mail2,
            mySqlite.execute(f"select count(1) from scores where created='{date_utils.today()}' ;")[0][0],
            mySqlite.execute(f"select count(1) from scores where status in ('已验证','已解决','已拒绝') ;")[0][0],
            mySqlite.execute(f"select count(1) from scores where status in ('重新打开','确认产品bug','新') ;")[0][0],
            mySqlite.execute(f"select count(1) from scores where closed='{date_utils.today()}';")[0][0],
            mySqlite.execute(f"select count(1) from scores where reject_num not in ('0', 0, '', '--');")[0][0]
        ]
    return numbs
